import sys

import json
import re
import pymssql
from selenium import webdriver
from selenium.webdriver.support.wait import WebDriverWait
import time
import decimal


class TMShop(object):
    def __init__(self, startTime, endTime, use):
        self.startTime = str(startTime)
        self.endTime = str(endTime)
        self.use = str(use)
        self.url = 'https://web.cbbs.tmall.com/'
        self.rootUrl = 'https://dataweb.cbbs.tmall.com/data/service/invoke.json?'
        options = webdriver.ChromeOptions()
        options.add_argument('--headless')
        options.add_experimental_option('excludeSwitches', ['enable-automation'])  # 隐藏程序模拟浏览器
        self.driver = webdriver.Chrome(options=options)
        self.wait = WebDriverWait(self.driver, 10)
        self.db = pymssql.connect(host='192.168.0.186', user='sa', password='987987abc#', port='1433',
                                  database='TopSystem_D11', charset="utf8")
        self.cursor = self.db.cursor()

    def login(self):
        self.driver.get("https://web.cbbs.tmall.com/")
        with open("cookies_tm.json", "r", encoding="utf8") as fp:
            cookies = json.loads(fp.read())
        for item in cookies:
            self.driver.add_cookie(item)

    def is_element(self, check, html):
        try:
            return re.findall(check, html)
        except Exception:
            return ""

    def is_Error(self, er, flag):
        if 'dataSource' in er and flag == 1:
            return er
        elif 'data' in er and flag == 2:
            return er
        else:
            return False

    def page_parse_ll(self):
        self.login()
        time.sleep(2)
        # 请求地区json
        self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_area')
        area_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        area_html = json.loads(area_html[0]).get('data')
        area_data = [ar.get('label') for ar in area_html]

        # 请求品牌json
        self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_brand' + '&optionAll=true')
        bank_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        bank_html = json.loads(bank_html[0]).get('data')
        bank_label = [ar.get('label') for ar in bank_html]
        self.bank_value = [ar.get('value') for ar in bank_html]
        print()
        print("爬虫开始")
        # 拼接链接
        # 请求三级类目的下拉列表
        for i in self.bank_value:
            self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_category_3&brandId=' + str(i))
            time.sleep(1)
            product_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
            product_html = json.loads(product_html[0]).get('data')
            product_html_label = [ii.get('label') for ii in product_html]
            self.product_html_value = [ii.get('value') for ii in product_html]
            time.sleep(1)

            for action in self.product_html_value:
                # 拼接的客户信息链接
                urlu = 'https://dataweb.cbbs.tmall.com/data/service/41/invoke.json' + '?brandId =' + str(
                    i) + '&cate3Id =' + str(action) + '&endTime' + str(
                    self.endTime) + '&id=sm_uv_overview' + '&logicArea=-99999' + '&startTime=' + str(self.startTime)
                self.driver.get(urlu.replace(' ', ''))
                time.sleep(1)
                # json页面的解析到10个数组的上一级
                consumer_html = self.is_element(r'<pre.*">(.*?)</pre>', self.driver.page_source)
                consumer_html = self.is_Error(json.loads(consumer_html[0]).get('data').get('views')[0].get('value'), 1)
                if consumer_html:
                    allClickUv = consumer_html.get('dataSource')[0].get('ext').get('overviewList')[0].get('data')[
                        0].get('value').replace('人', '')
                    ipvUv = consumer_html.get('dataSource')[1].get('ext').get('overviewList')[0].get('data')[0].get(
                        'value').replace('人', '')
                    cartUserNum = consumer_html.get('dataSource')[2].get('ext').get('overviewList')[0].get('data')[
                        0].get('value').replace('人', '')
                    ipvClickUv = consumer_html.get('dataSource')[3].get('ext').get('overviewList')[0].get('data')[
                        0].get('value').replace('人', '')
                    listClickUv = consumer_html.get('dataSource')[4].get('ext').get('overviewList')[0].get('data')[
                        0].get('value').replace('人', '')
                    payUserNum = consumer_html.get('dataSource')[5].get('ext').get('overviewList')[0].get('data')[
                        0].get('value').replace('人', '')
                    payMoney = consumer_html.get('dataSource')[6].get('ext').get('overviewList')[0].get('data')[0].get(
                        'value').replace('元', '')
                    cartRatio = consumer_html.get('dataSource')[7].get('ext').get('overviewList')[0].get('data')[0].get(
                        'value').replace('人', '')
                    payRatio = consumer_html.get('dataSource')[8].get('ext').get('overviewList')[0].get('data')[0].get(
                        'value').replace('人', '')
                    uvValue = consumer_html.get('dataSource')[9].get('ext').get('overviewList')[0].get('data')[0].get(
                        'value').replace('元', '')
                else:
                    allClickUv = ""
                    ipvUv = ""
                    cartUserNum = ""
                    ipvClickUv = ""
                    listClickUv = ""
                    payUserNum = ""
                    payMoney = ""
                    cartRatio = ""
                    payRatio = ""
                    uvValue = ""
                time.sleep(3)
                print(i, action, self.endTime, self.use, allClickUv, ipvUv, cartUserNum, ipvClickUv, listClickUv, payUserNum,
                      payMoney, cartRatio, payRatio, uvValue)

                # 数据不为空则入库操作
                if allClickUv != "":
                    try:
                        sql = 'insert into TS_UV_OverViewNew(brandId, CategoryId, AccessDate, Account, allClickUv, \
                        ipvUv, cartUserNum, ipvClickUv, listClickUv, payUserNum, payMoney, cartRatio, payRatio, uvValue)\
                                values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                        self.cursor.execute(sql, (i, action, self.endTime, self.use, allClickUv, ipvUv, \
                        cartUserNum, ipvClickUv, listClickUv, payUserNum, payMoney, cartRatio, payRatio, uvValue))
                    except Exception:
                        print("入库程序失败请找开发人员")
                        self.db.rollback()
                    self.db.commit()
            time.sleep(5)
        print("爬虫结束")

    def page_parsr_hy(self):
        # self.login()
        # time.sleep(2)
        # # 请求地区json
        # self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_area')
        # area_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        # area_html = json.loads(area_html[0]).get('data')
        # area_data = [ar.get('label') for ar in area_html]
        #
        # # 请求品牌json
        # self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_brand' + '&optionAll=false')
        # bank_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        # bank_html = json.loads(bank_html[0]).get('data')
        # bank_label = [ar.get('label') for ar in bank_html]
        # self.bank_value = [ar.get('value') for ar in bank_html]

        self.login()
        time.sleep(2)
        # 请求地区json
        self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_area')
        area_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        area_html = json.loads(area_html[0]).get('data')
        area_data = [ar.get('label') for ar in area_html]

        # 请求品牌json
        self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_brand' + '&optionAll=true')
        bank_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
        bank_html = json.loads(bank_html[0]).get('data')
        bank_label = [ar.get('label') for ar in bank_html]
        self.bank_value = [ar.get('value') for ar in bank_html]
        print()
        print("爬虫开始")
        # 拼接链接
        # 请求三级类目的下拉列表
        for i in self.bank_value:
            self.driver.get(str(self.rootUrl) + 'serviceId=sm_option_category_3&brandId=' + str(i))
            time.sleep(1)
            product_html = re.findall(r'<pre.*">(.*?)</pre>', self.driver.page_source)
            product_html = json.loads(product_html[0]).get('data')
            product_html_label = [ii.get('label') for ii in product_html]
            self.product_html_value = [ii.get('value') for ii in product_html]
            time.sleep(1)

            for action in self.product_html_value:
                # 拼接的客户信息链接
                urlu = 'https://dataweb.cbbs.tmall.com/data/service/invoke.json' + \
                       '?query.brandId=' + str(i) + \
                       '&query.cate3Id=' + str(action) + \
                       '&query.endTime=' + str(self.endTime) + \
                       '&query.logicArea=-99999' + \
                       '&query.startTime=' + str(self.startTime) + \
                       '&serviceId=sm_member_analysis_overview'
                self.driver.get(urlu.replace(' ', ''))
                user_html = self.is_element(r'<pre.*">(.*?)</pre>', self.driver.page_source)
                user_html = self.is_Error((json.loads(user_html[0]).get('data')[0].get('value')), 2)
                if user_html:
                    buyerAll = user_html.get('data')[0].get('description').replace('人', '')
                    buyerNew = user_html.get('data')[1].get('description').replace('人', '')
                    buyerOld = user_html.get('data')[2].get('description').replace('人', '')
                    payAmtPerByrAll = user_html.get('data')[3].get('description').replace('元', '')
                    payAmtPerByrNew = user_html.get('data')[4].get('description').replace('元', '')
                    payAmtPerByrOld = user_html.get('data')[5].get('description').replace('元', '')
                else:
                    buyerAll = ""
                    buyerNew = ""
                    buyerOld = ""
                    payAmtPerByrAll = ""
                    payAmtPerByrNew = ""
                    payAmtPerByrOld = ""
                # 数据存在则入库
                if user_html:
                    try:
                        sql = 'insert into TS_MemberAnalysis_OverViewNew(buyerAll, buyerNew, buyerOld, payAmtPerByrAll,\
                        payAmtPerByrNew, payAmtPerByrOld, brandId, CategoryId, AccessDate,Account) \
                        values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) '
                        self.cursor.execute(sql, (buyerAll, buyerNew, buyerOld, payAmtPerByrAll, payAmtPerByrNew, \
                                                  payAmtPerByrOld, i, action, self.endTime[:-3], self.use))
                    except Exception:
                        print("入库失误请找开发人员")
                        self.db.rollback()
                    self.db.commit()
                print(buyerAll, buyerNew, buyerOld, payAmtPerByrAll, payAmtPerByrNew, payAmtPerByrOld, i, action,
                      self.endTime[:-3], self.use)
            time.sleep(2)
        print("爬虫结束")

    def commit_order(self):
        self.db.commit()
        self.db.close()


if __name__ == '__main__':
    user = input("请输入操作的账号")
    choose = input("请输入要使用的操作（1.流浪分析更新2.会员分析更新）")

    startdate = input("请输入要查询的开始日期格式为（xxxx-xx-xx）")
    enddate = input("请输入要查询的结束日期格式为（xxxx-xx-xx）")
    tm = TMShop(startdate, enddate, user)
    if choose == str("1"):
        tm.page_parse_ll()
    elif choose == str("2"):
        tm.page_parsr_hy()
    else:
        print("输入错误")
        sys.exit()

    is_Send = input("是否保存数据（y/n）")
    if is_Send == str("y") or is_Send == str("Y"):
        tm.commit_order()
        print("数据保存成功")
    else:
        print("数据保存失败")
        sys.exit()
